package com.itsm.service.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itsm.entity.Process;
import com.itsm.entity.Process1;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface  ProcessMapper extends BaseMapper<Process> {

    /**
     * 所有工单
     * @param name
     * @param columnName
     * @return
     */
    @Select("SELECT count(1) FROM process WHERE ${columnName} = #{name} and is_accepted = 1 ")
    String getAllCount(String name,String columnName);

    /**
     *
     * @param name
     * @param columnName
     * @return
     */
    @Select("SELECT count(1) FROM process WHERE ${columnName} = #{name} and is_accepted = 1 and state = 1 " )
    String getCount1(String name,String columnName);

    @Select("SELECT * FROM process WHERE is_accepted = 1 and state = 1 and is_accepted = 1 " )
    List<Process> getUndone();

//    @Select("SELECT * FROM process WHERE is_delete = 1 and is_accepted = 0 and state = 0 and plant = 1 " )
    @Select("select p.* FROM process p left join  approve  a on p.process_no = a.process_no " +
        "WHERE (is_delete = 1 and state = 0  and is_accepted = 0 and approve_time is not null " +
        "and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) and a.status !=2 and plant = 1 ) " +
        "or (is_delete = 1 and state = 0  and is_accepted = 0 and a.process_no is null and plant = 1 ) " +
        "order by p.process_no desc")
    List<Process> getUndistributed();

    //获取所有未审批的工单
    @Select("select p.* from process p left join approve a on p.process_no = a.process_no where p.is_delete = 1 and   a.status = 0 and a.sub_time is not null")
    List<Process> getUnApprove();

    @Select("select approver from  approve  where status = 0 and sub_time is not null and process_no = #{processNo}")
    String getUnApprove1(String processNo);
    @Select("SELECT * FROM process WHERE is_delete = 1 and is_accepted = 0 and state = 1  and accept_time is null and respa_name is not null" )
    List<Process> getUnaccepted();
    @Select("SELECT count(1) FROM process WHERE ${columnName} = #{name} and is_accepted = 1 and state = 2" )
    String getCount2(String name,String columnName);

    @Select("SELECT count(1) FROM process WHERE ${columnName} = #{name} and is_accepted = 1 and state = 2 and score is null" )
    String getCount3(String name,String columnName);

    @Select("SELECT count(1) FROM process WHERE ${columnName} = #{name} and is_accepted = 1 and state = 2 and score is not null" )
    String getCount4(String name,String columnName);



    //获取各状态的工单
    @Select("SELECT * FROM process p  WHERE  p.is_delete = 1 and state = #{state} and is_accepted = #{is} and plant = #{plant} order by p.process_no desc" )
    List<Process> get1(Integer state,Integer is,Integer plant);

    //获取未派单的工单，区分工厂
    @Select("select p.* FROM process p left join  approve  a on p.process_no = a.process_no " +
            "WHERE (is_delete = 1 and state = 0  and is_accepted = 0 and plant = #{plant} and approve_time is not null " +
            "and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) and a.status !=2) " +
            "or (is_delete = 1 and state = 0  and is_accepted = 0 and plant = #{plant} and a.process_no is null) " +
            "order by p.process_no desc")
    List<Process> getUnDispatch2(Integer plant);

    //获取未派单的工单
    @Select("select p.* FROM process p left join  approve  a on p.process_no = a.process_no " +
            "WHERE (is_delete = 1 and state = 0  and is_accepted = 0 and approve_time is not null " +
            "and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) and a.status !=2 ) " +
            "or (is_delete = 1 and state = 0  and is_accepted = 0 and a.process_no is null) " +
            "order by p.process_no desc")
    List<Process> getUnDispatch3();

    //获取不同工厂的工单
    @Select("SELECT * FROM process p left join question q on p.question_type = q.id WHERE p.plant = #{plant} and p.is_delete = 1 order by p.process_no desc" )
    List<Process> getAllNo(Integer plant);

    @Select("SELECT p.*,a.approve_time,a.id FROM process p left join  approve  a on p.process_no = a.process_no and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no)  " +
            "WHERE p.plant = #{plant} and is_delete = 1 order by process_no desc" )
    List<Process1> getAllNo1(Integer plant);

    @Select("SELECT p.*,a.approve_time,a.id FROM process p left join  approve  a on p.process_no = a.process_no and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no)  " +
            "WHERE p.plant = #{plant} and is_delete = 1 and (create_name like concat('%',#{userName},'%') or subject like concat('%',#{userName},'%') or content like concat('%',#{userName},'%')) order by process_no desc" )
    List<Process1> getAllNo2(Integer plant,String userName);

    //获取所有的工单
    @Select("SELECT * FROM process WHERE is_delete = 1 order by process_no desc" )
    List<Process> getAll();

    //获取所有工单，包含审批时间
    @Select("SELECT p.*,a.approve_time,a.id FROM process p left join  approve  a on p.process_no = a.process_no and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) WHERE is_delete = 1  order by process_no desc" )
    List<Process1> getAll1();

    //按条件查询工单，包含审批时间
    @Select("SELECT p.*,a.approve_time,a.id FROM process p left join  approve  a on p.process_no = a.process_no and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) WHERE is_delete = 1 and (create_name like concat('%',#{userName},'%') or subject like concat('%',#{userName},'%') or content like concat('%',#{userName},'%')) order by process_no desc" )
    List<Process1> getAll2(String userName);


    //获取未审批的工单,区分工厂
    @Select("SELECT p.*,a.approve_time,a.id FROM process p left join  approve  a on p.process_no = a.process_no  WHERE is_delete = 1 and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) and p.plant = #{plant} and approve_time is null order by p.process_no desc" )
    List<Process1> getAllUnApp(Integer plant);


    //获取未审批的工单,不区分工厂
    @Select("SELECT p.*,a.approve_time,a.id FROM process p left join  approve  a on p.process_no = a.process_no  WHERE is_delete = 1 and a.id = (select max(id) from approve a2 where p.process_no = a2.process_no) and approve_time is null order by p.process_no desc" )
    List<Process1> getAllUnApp1();

    //弃用
    //获取所有审批的工单
    @Select("select distinct p.* from process p left join approve a on p.process_no = a.process_no where a.count = (select max(count) from approve a2 where p.process_no = a2.process_no)  and p.is_delete = 1 and approver = #{userid} order by p.process_no desc")
    List<Process> getAllApprove(String userid);

    //按照审批人，获取所有已审批的工单
    @Select("select distinct p.* from process p left join approve a on p.process_no = a.process_no where a.status= 1 and a.count = (select max(count) from approve a2 where p.process_no = a2.process_no)  and p.is_delete =1 and approver = #{name} order by p.process_no desc ")
    List<Process> getAllApp(String name);

    //按照审批人，获取所有未审批的工单
    @Select("select distinct p.* from process p left join approve a on p.process_no = a.process_no where a.status = 0 and a.count = (select max(count) from approve a2 where p.process_no = a2.process_no)  and p.is_delete = 1 and approver = #{name} order by p.process_no desc ")
    List<Process> getAllUnApp2(String name);

    //按照审批人和IT主管，获取所有未审批的工单
    @Select("select distinct p.* from process p left join approve a on p.process_no = a.process_no where (a.status = 0 and a.sub_time is not null and p.is_delete = 1 and approver = #{name}) or (a.status = 0 and a.step = 2 and a.sub_time is not null and p.is_delete = 1 and p.plant = #{plant}) order by p.process_no desc ")
    List<Process> getAllUnApp3(String name,Integer plant);
    @Select("select distinct p.* from process p left join approve a on p.process_no = a.process_no where approver = #{userid} and question_type = 2  and p.is_delete =1 and a.status = 0")
    List<Process> getAllUnApprove(String userid);



}
